Лабораторна робота №8 з курсу “Організація баз даних і знань” На тему: Робота із вкладеними запитами. Приклад 1. Оператор: SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'; Результат: +-----------+ | order_num | +-----------+ | 20005 | | 20007 | +-----------+ Приклад 2. Оператор: SELECT cust_id FROM orders WHERE order_num IN (20005,20007); Результат: +---------+ | cust_id | +---------+ | 10001 | | 10004 | +---------+ Приклад 3. Оператор: SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'); Результат: +---------+ | cust_id | +---------+ | 10001 | | 10004 | +---------+ Приклад 4. Оператор: SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2')); Результат: +----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | Coyote Inc. | Y Lee | | Yosemite Place | Y Sam | +----------------+--------------+ Приклад 5. Оператор: SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name; Результат: +----------------+------------+--------+ | cust_name | cust_state | orders | +----------------+------------+--------+ | Coyote Inc. | MI | 2 | | E Fudd | IL | 1 | | Mouse House | OH | 0 | | Wascals | IN | 1 | | Yosemite Place | AZ | 1 | +----------------+------------+--------+ Приклад 6. Оператор: SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE cust_id = cust_id) AS orders FROM customers ORDER BY cust_name; Результат: +----------------+------------+--------+ | cust_name | cust_state | orders | +----------------+------------+--------+ | Coyote Inc. | MI | 5 | | E Fudd | IL | 5 | | Mouse House | OH | 5 | | Wascals | IN | 5 | | Yosemite Place | AZ | 5 | +----------------+------------+--------+ Література: SQL Полное руководство – Грофф Ю Н Артеменко MySQL Справочник по язык Справочник по SQL Р.Яргер, Дж.Риз, Т.Кинг MySQL и mSAL. Базы данныз для небольших предприятий и интернета.